const express = require('express');
const mysql = require("mysql");
const app = express();
app.use(express.static("www"));


const mysqli = mysql.createConnection({
    host: "localhost", //ip
    user: "root",  //用户名
    password: "root", //密码
    database: "admin" //要连接的数据库名称
})


mysqli.connect(function (err) {
    if (err) {
        console.log("连接数据库失败");
    } else {
        console.log("连接数据库成功");
    }
})

app.get("/login", function (req, res) {
    console.log(req.query);
    // var sql = `insert into users(username, password) values ("${req.query.username}","${req.query.password}")`;
    var sql = "select * from users";
    mysqli.query(sql, function (err, data) {
        if (err) {
            console.log("sql语句执行失败");
        } else {
            var flag = false;
            var pass = null;
            for (let i = 0; i < data.length; i++) {
                if (data[i].username == req.query.username) {
                    flag = true;
                    pass = data[i].username;
                };
            }
            if (flag) {
                if (pass == req.query.password) {
                    res.send("1")
                } else {
                    res.send("0")
                }
            } else {
                res.send("11");
            }
        }
    })
})
app.get("/res", function (req, res) {
    var sql = "select * from users";
    mysqli.query(sql, function (err, data) {
        if (err) {
            console.log("resSql1语句执行失败");
        } else {
            var flag = true;
            for (let i = 0; i < data.length; i++) {
                if (data[i].username == req.query.username) {
                    flag = false;
                };
            }
            if (flag) {
                var sql2 = `insert into users(username, password) values ("${req.query.username}","${req.query.password}")`;
                mysqli.query(sql2, function (err, data) {
                    if (err) {
                        console.log("resSql2语句执行失败");
                    } else {
                        res.send("1")
                    }
                })
            } else {
                res.send("0");
            }
        }
    })
})
app.get("/home", function (req, res) {
    var sql = ''
    // var page = req.query.page || 1;
    // var limit = req.query.limit || 20;
    // var num = (page - 1) * limit + 1;
    if (req.query.year == '0') {
        console.log(req.query);
        // sql = `select * from contentList limit ${num},${limit}`
        sql = `select * from contentList`
    } else if (req.query.banji == "0") {
        sql = `select * from contentlist where period="${req.query.year}" and spercialty="${req.query.zhuanye}" `;
    } else if (req.query.banji == "1") {
        sql = `select * from contentlist where period="${req.query.year}" and spercialty="${req.query.zhuanye}" and squad="一班"`;
    } else if (req.query.banji == "2") {
        sql = `select * from contentlist where period="${req.query.year}" and spercialty="${req.query.zhuanye}" and squad="二班"`;
    }else if(req.query.year == 'undefined' && req.query.banji == "1"){
        sql = `select * from contentlist where squad="一班"`;
    }else if(req.query.year == 'undefined' && req.query.banji == "2"){
        sql = `select * from contentlist where squad="二班"`;
    }else{
        sql = `select * from contentList`;
    }
    mysqli.query(sql, function (err, data) {
        if (err) {
            console.log("sql语句执行失败");
        } else {
            res.json(data);
        }
    })
})
app.get("/del", function (req, res) {
    var sql = `delete from contentlist where id=${req.query.id}`;
    mysqli.query(sql, function (err, data) {
        if (err) {
            console.log("sql语句执行失败");
        }
    })
})
var list = [];
app.get("/getOne", function (req, res) {
    console.log(req.query);
    list.push(req.query);
    // var sql = `insert into contentlist(period,spercialty,squad,username,age,sex,phone,native) values ("${req.query.year}","${req.query.spercialty}","${req.query.squad}","${req.query.username}","${req.query.age}","${req.query.sex}","${req.query.phone}","${req.query.native}")`
    // mysqli.query(sql, function (err, data) {
    //     if (err) {
    //         console.log("sql语句执行失败");
    //     }
    // })
})



app.listen(3000, () => {
    // 服务器运行成功后的回调函数
    console.log("http://localhost:3000");
})